﻿using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Xml.Linq;

namespace FinalPointSet.Model.Model.FieldMapping
{
    public class SQLFormatter
    {
        public static string GetSelectSQL(Table table, FormatTemplate formatTemplate)
        {
            return string.Empty;
        }

        /// <summary>
        /// 获取SQL
        /// </summary>
        /// <param name="table"></param>
        /// <param name="createTemplate"></param>
        /// <returns></returns>
        public static string GetSQL(Table table, FormatTemplate formatTemplate, DataSourceMappingRule mappingRule)
        {
            string template = new(formatTemplate.Content);
            //格式化字段自定义样式
            template = FormatFieldStyle(table, mappingRule, template);
            //格式化附加输出
            template = FormatExtraOutputStyle(table, mappingRule, template);

            string sb = new(template);
            //全字段(名称+类型+备注)
            string fieldsStr = GetFieldsString(table.Fields, mappingRule);
            //字段名称
            string fieldsName = GetFieldsName(table.Fields);
            //构建SQL文本
            return sb.Replace("${DATABASE}", table.Database)
                     .Replace("${TABLE_NAME}", table.Name)
                     .Replace("${FIELDS}", fieldsStr)
                     .Replace("${COMMENT}", table.Comment)
                     .Replace("${FIELD_NAMES}", fieldsName)
                     .ToString();
        }

        /// <summary>
        /// 获取所有字段的名称
        /// </summary>
        /// <param name="fields"></param>
        /// <returns></returns>
        public static string GetFieldsName(List<Field> fields)
        {
            StringBuilder result = new();
            const int nameSpaceLen = 4;
            for (int i = 0; i < fields.Count; i++)
            {
                if (i == 0)
                {
                    string line = new string(' ', nameSpaceLen + 1) + fields[i].Name;
                    if (fields.Count > 1)
                    {
                        line += '\n';
                    }
                    result.Append(line);
                }
                else if (i == fields.Count - 1)
                {
                    result.Append(new string(' ', nameSpaceLen) + "," + fields[i].Name);
                }
                else
                {
                    result.Append(new string(' ', nameSpaceLen) + "," + fields[i].Name + "\n");
                }
            }
            return result.ToString();
        }
        /// <summary>
        /// 获取所有的字段的SQL语句
        /// </summary>
        /// <returns>字段的SQL语句字符串</returns>
        public static string GetFieldsString(List<Field> fields, DataSourceMappingRule mappingRule, bool fieldCommentVisible = true)
        {
            StringBuilder sb_result = new();
            const int cnst_i_name_before_space_cnt = 4,    //名称前的空格个数
                      cnst_i_type_before_space_cnt = 4,    //类型前的空格个数
                      cnst_i_cmt_before_space_cnt = 2,     //备注前的空格个数
                      cnst_i_cmt_after_space_cnt = 1;      //备注后的空格个数

            string[] s_arr_realtype = new string[fields.Count];
            List<FieldMappingKeyValuePair> pairs = [.. mappingRule.FieldTypeMapping];
            for (int i = 0; i < s_arr_realtype.Length; i++)
            {
                s_arr_realtype[i] = fields[i].Type;
                //若字典不为空,则将字段的类型映射为目标类型
                if (pairs != null && pairs.Count > 0)
                {
                    foreach (var entry in pairs)
                    {
                        if (fields[i].Type.Equals(entry.SourceFieldType.ToLower()))
                        {
                            s_arr_realtype[i] = string.Format(entry.TargetFieldType, fields[i].Integer, fields[i].Decimal);
                            break;
                        }
                    }
                }
            }

            //1.遍历字段,获取最长的名称和类型,用于格式化输出
            int i_max_name_len = 0, i_max_type_len = 0;
            for (int i = 0; i < fields.Count; i++)
            {
                //判断名称长度
                if (fields[i].Name != null && fields[i].Name.Length > i_max_name_len)
                {
                    i_max_name_len = fields[i].Name.Length;
                }
                //判断类型长度
                if (s_arr_realtype[i] != null && s_arr_realtype[i].Length > i_max_type_len)
                {
                    i_max_type_len = s_arr_realtype[i].Length;
                }
            }

            //2.遍历字段,输出语句SQL
            for (int i = 0; i < fields.Count; i++)
            {
                //1.对字段的值进行处理,防止空引用
                Field cls_field = fields[i];
                cls_field.Name ??= string.Empty;
                cls_field.Type ??= string.Empty;
                cls_field.Comment ??= string.Empty;
                cls_field.Attribute ??= string.Empty;

                //2.根据最长的字段名称、类型的长度补充空格
                int i_offset_name_space_cnt = i_max_name_len - cls_field.Name.Length;
                int i_offset_type_space_cnt = i_max_type_len - s_arr_realtype[i].Length;
                //3.类型
                string s_type = new string(' ', i_offset_name_space_cnt + cnst_i_type_before_space_cnt) + s_arr_realtype[i];

                //4.额外属性
                string s_extra = "";
                if (!string.Empty.Equals(cls_field.Attribute))
                {
                    s_extra = new string(' ', 1) + cls_field.Attribute;
                }

                //5.备注
                string s_cmt = string.Empty;
                if (fieldCommentVisible)
                {
                    s_cmt = new string(' ', i_offset_type_space_cnt + cnst_i_cmt_before_space_cnt) + "COMMENT"
                    + new string(' ', cnst_i_cmt_after_space_cnt) + "'" + cls_field.Comment + "'";
                }

                //6.拼接所有字段值
                string s_name;
                string s_result;
                //首行
                if (i == 0)
                {
                    s_name = new string(' ', cnst_i_name_before_space_cnt + 1) + cls_field.Name;
                    s_result = s_name + s_type + s_extra + s_cmt;
                    if (fields.Count > 1)
                    {
                        s_result += "\n";
                    }
                }
                //最后一行
                else if (i == fields.Count - 1)
                {
                    s_name = new string(' ', cnst_i_name_before_space_cnt) + "," + cls_field.Name;
                    s_result = s_name + s_type + s_extra + s_cmt;
                }
                //其他行
                else
                {
                    s_name = new string(' ', cnst_i_name_before_space_cnt) + "," + cls_field.Name;
                    s_result = s_name + s_type + s_extra + s_cmt + "\n";
                }
                sb_result.Append(s_result);
            }
            return sb_result.ToString();
        }

        /// <summary>
        /// 根据指定的格式, 直接返回所有字段为字符串
        /// </summary>
        /// <param name="fields"></param>
        /// <param name="format"></param>
        /// <returns></returns>
        public static string GetFieldsString(List<Field> fields, DataSourceMappingRule mappingRule, string format)
        {
            string[] s_arr_realtype = new string[fields.Count];
            List<FieldMappingKeyValuePair> pairs = [.. mappingRule.FieldTypeMapping];
            for (int i = 0; i < s_arr_realtype.Length; i++)
            {
                s_arr_realtype[i] = fields[i].Type;
                //若字典不为空,则将字段的类型映射为目标类型
                if (pairs != null && pairs.Count > 0)
                {
                    foreach (var entry in pairs)
                    {
                        if (fields[i].Type.Equals(entry.SourceFieldType.ToLower()))
                        {
                            s_arr_realtype[i] = string.Format(entry.TargetFieldType, fields[i].Integer, fields[i].Decimal);
                            break;
                        }
                    }
                }
            }

            StringBuilder sbResult = new();
            //2.遍历字段,输出语句SQL
            for (int i = 0; i < fields.Count; i++)
            {
                //1.对字段的值进行处理,防止空引用
                Field cls_field = fields[i];
                cls_field.Name ??= string.Empty;
                cls_field.Type ??= string.Empty;
                cls_field.Comment ??= string.Empty;
                cls_field.Attribute ??= string.Empty;
                string result = format.Replace("${FIELD_NAME}", cls_field.Name)
                                      .Replace("${FIELD_TYPE}", cls_field.Type)
                                      .Replace("${FIELD_COMMENT}", cls_field.Comment);
                //最后一行
                if (i == fields.Count - 1)
                {
                    sbResult.Append(result);
                }
                else
                {
                    sbResult.AppendLine(result);
                }
            }

            return sbResult.ToString();
        }

        /// <summary>
        /// 格式化自定义字段样式
        /// </summary>
        /// <param name="table"></param>
        /// <param name="mappingRule"></param>
        /// <param name="template"></param>
        /// <returns></returns>
        private static string FormatFieldStyle(Table table, DataSourceMappingRule mappingRule, string template)
        {
            //判断是否需要自定义字段格式化样式
            string pattern = @"<FieldStyle>(.*?)<\/FieldStyle>";
            string patternReplace = @"<FieldStyle>[\s\S]*?</FieldStyle>";
            Regex regex = new(pattern, RegexOptions.Singleline);
            MatchCollection matches = regex.Matches(template);
            if (matches.Count > 0)
            {
                foreach (Match match in matches.Cast<Match>())
                {
                    string matchContent = match.Groups[0].Value;
                    if (!match.Success)
                        continue;
                    XDocument doc = XDocument.Parse(matchContent);
                    string strFieldCommentVisible = doc.Root?.Element("FieldCommentVisible")?.Value ?? "";
                    bool fieldCommentVisible = "True".Equals(strFieldCommentVisible);
                    Debug.Print($"SQLFormatter.GetSQL(),fieldCommentVisible={fieldCommentVisible}");
                    string customFieldStr = GetFieldsString(table.Fields, mappingRule, fieldCommentVisible);
                    template = Regex.Replace(template, patternReplace, customFieldStr);
                }
            }

            return template;
        }

        /// <summary>
        /// 格式化附加输出
        /// </summary>
        /// <param name="table"></param>
        /// <param name="mappingRule"></param>
        /// <param name="template"></param>
        /// <returns></returns>
        private static string FormatExtraOutputStyle(Table table, DataSourceMappingRule mappingRule, string template)
        {
            //判断是否需要额外输出字段数据
            string patternExtraOutpt = @"<ExtraOutputStyle>(.*?)<\/ExtraOutputStyle>";
            string patternExtraOutptReplace = @"<ExtraOutputStyle>[\s\S]*?</ExtraOutputStyle>";
            Regex regexExtraOutpt = new(patternExtraOutpt, RegexOptions.Singleline);
            MatchCollection matcheExtraOutpts = regexExtraOutpt.Matches(template);
            if (matcheExtraOutpts.Count > 0)
            {
                foreach (Match match in matcheExtraOutpts.Cast<Match>())
                {
                    string matchContent = match.Groups[1].Value;
                    if (!match.Success)
                        continue;

                    string customFieldStr = GetFieldsString(table.Fields, mappingRule, matchContent);
                    template = Regex.Replace(template, patternExtraOutptReplace, customFieldStr);
                }
            }

            return template;
        }
    }
}
